package com.ramnight.unify.boot.jdbc;

import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.collections4.MapUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.google.common.collect.ImmutableSet;
import com.ramnight.unify.kit.UnifyConverter;
import com.ramnight.unify.kit.UnifyMap;
import com.ramnight.unify.kit.exception.UnifyException;

@SuppressWarnings("unchecked")
public class UnifyJdbc {
	private static final String SERIAL_PARAM_PREFIX = "x";
	private static final String ID = "id";

	private NamedParameterJdbcTemplate jdbc;

	public UnifyJdbc(DataSource datasource) {
		this.jdbc = new NamedParameterJdbcTemplate(datasource);
	}

	public Integer queryInteger(String sql, Object param) {
		Object obj = getValueFromList(queryList(sql, param));
		return UnifyConverter.toInteger(obj);
	}

	public Long queryLong(String sql, Object param) {
		Object obj = getValueFromList(queryList(sql, param));
		return UnifyConverter.toLong(obj);
	}

	public String queryString(String sql, Object param) {
		Object obj = getValueFromList(queryList(sql, param));
		return UnifyConverter.toString(obj);
	}

	public Integer xqueryInteger(String sql, Object... params) {
		Object obj = getValueFromList(xqueryList(sql, params));
		return UnifyConverter.toInteger(obj);
	}

	public Long xqueryLong(String sql, Object... params) {
		Object obj = getValueFromList(xqueryList(sql, params));
		return UnifyConverter.toLong(obj);
	}

	public String xqueryString(String sql, Object... params) {
		Object obj = getValueFromList(xqueryList(sql, params));
		return UnifyConverter.toString(obj);
	}

	// 取第一个row的第一个column的值（多row或column报错）
	private Object getValueFromList(List<UnifyMap> list) {
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		if (ListUtils.emptyIfNull(list).size() != 1 || MapUtils.emptyIfNull(list.iterator().next()).size() != 1) {
			throw new UnifyException("query result num error");
		}
		return list.iterator().next().values().iterator().next();
	}

	public UnifyMap querySingle(String sql, Object param) {
		return findFirst(queryList(sql, param));
	}

	public <T> T querySingle(String sql, Object param, Class<T> resultClazz) {
		return findFirst(queryList(sql, resultClazz, param));
	}

	public UnifyMap xquerySingle(String sql, Object... params) {
		return findFirst(xqueryList(sql, params));
	}

	public <T> T xquerySingle(String sql, Class<T> resultClazz, Object... params) {
		return findFirst(xqueryList(sql, resultClazz, params));
	}

	public List<UnifyMap> queryList(String sql, Object param) {
		SqlParameterSource paramSource = objToParamSource(param);
		return doQueryList(sql, paramSource, UnifyMapRowMapper.INSTANCE);
	}

	public <T> List<T> queryList(String sql, Class<T> resultClazz, Object param) {
		SqlParameterSource paramSource = objToParamSource(param);
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(resultClazz);
		return doQueryList(sql, paramSource, rowMapper);
	}

	public List<UnifyMap> xqueryList(String sql, Object... params) {
		SqlParameterSource paramSource = objsToParamSource(params);
		return doQueryList(sql, paramSource, UnifyMapRowMapper.INSTANCE);
	}

	public <T> List<T> xqueryList(String sql, Class<T> resultClazz, Object... params) {
		SqlParameterSource paramSource = objsToParamSource(params);
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(resultClazz);
		return doQueryList(sql, paramSource, rowMapper);
	}

	private <T> List<T> doQueryList(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper) {
		return ListUtils.emptyIfNull(jdbc.query(sql, paramSource, rowMapper));
	}

	public int xupdate(String sql, Object... params) {
		SqlParameterSource paramSource = objsToParamSource(params);
		return jdbc.update(sql, paramSource);
	}

	public int update(String sql, Object param) {
		SqlParameterSource paramSource = objToParamSource(param);
		return jdbc.update(sql, paramSource);
	}

	public void updateBatchByMapList(String sql, List<UnifyMap> paramList) {
		List<SqlParameterSource> psList = ListUtils.emptyIfNull(paramList).stream().map(m -> new MapSqlParameterSource(m)).collect(Collectors.toList());
		jdbc.batchUpdate(sql, psList.toArray(new SqlParameterSource[psList.size()]));
	}

	public <T> void updateBatchByBeanList(String sql, List<T> paramList) {
		List<SqlParameterSource> psList = ListUtils.emptyIfNull(paramList).stream().map(b -> new BeanPropertySqlParameterSource(b)).collect(Collectors.toList());
		jdbc.batchUpdate(sql, psList.toArray(new SqlParameterSource[psList.size()]));
	}

	public int insertByEntity(Object entity) {
		return update(SqlGen.getInsertSql(entity.getClass(), Collections.emptySet()), entity);
	}

	public <T> void insertBatchByEntity(List<Object> entityList) {
		updateBatchByBeanList(SqlGen.getInsertSql(entityList.iterator().next().getClass(), Collections.emptySet()), entityList);
	}

	public int insertWithoutIdByEntity(Object entity) {
		return update(SqlGen.getInsertSql(entity.getClass(), ImmutableSet.of(ID)), entity);
	}

	public <T> void insertWithoutIdBatchByEntity(List<Object> entityList) {
		updateBatchByBeanList(SqlGen.getInsertSql(entityList.iterator().next().getClass(), ImmutableSet.of(ID)), entityList);
	}

	private SqlParameterSource objToParamSource(Object param) {
		SqlParameterSource paramSource = null;
		if (param instanceof Map) {
			paramSource = new MapSqlParameterSource((Map<String, ?>) param);
		} else {
			paramSource = new BeanPropertySqlParameterSource(param);
		}
		return paramSource;
	}

	private SqlParameterSource objsToParamSource(Object[] params) {
		UnifyMap map = UnifyMap.of();
		for (int i = 0; i < params.length; i++) {
			if (i == 0) { // 0位可省略序号
				map.add(SERIAL_PARAM_PREFIX, params[i]);
			}
			map.add(SERIAL_PARAM_PREFIX + i, params[i]);
		}
		return new MapSqlParameterSource(map);
	}

	public NamedParameterJdbcTemplate origin() {
		return jdbc;
	}

	private <T> T findFirst(List<T> list) {
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return list.get(0);
	}
}
